package com.jh.wdbc.service;

import java.io.StringReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;

import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.select.Select;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jh.wdbc.model.ConnectionInfo;
import com.jh.wdbc.model.ResultModel;

@Service
public class ExecutionService {
	private static final int maxRow = 100;
	@Autowired
	private DataBaseConnectionService connectionService;

	public ResultModel executeQuery(String query, String connectionName) throws Exception {
		return executeQuery(query, connectionName, true);
	}

	private String[] names = {"TABLE"};
	public ResultModel executeQuery(String query, String connectionName, boolean isLimit) throws Exception {
		System.out.println(query);
		ConnectionInfo connectionInfo = connectionService.getConnectionInfo(connectionName);
		Connection connection = connectionInfo.getConnection();
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		ResultSetMetaData rsmd = rs.getMetaData();
		int colSize = rsmd.getColumnCount();
		LinkedList<String> columns = new LinkedList<String>();
		HashSet<String> tableSet = new HashSet<String>();
		for (int i = 1; i <= colSize; i++) {
			columns.add(rsmd.getColumnName(i));
			tableSet.add(rsmd.getTableName(i));
		}
		if (connectionInfo.getDbType().contains("Oracle")){
			CCJSqlParserManager pm = new CCJSqlParserManager();
			net.sf.jsqlparser.statement.Statement statement = pm.parse(new StringReader(query));
			if(statement instanceof Select){
				Select selectStmt = (Select)statement;
				TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
				List tableList = tablesNamesFinder.getTableList(selectStmt);
				tableSet.clear();
				tableSet.addAll(tableList);
			}
		}
		
		LinkedList<LinkedList<String>> datas = null;

		if (connectionInfo.getDbType().contains("Oracle") && "MS949".equals(connectionInfo.getRawEncoding())) {
			datas = extractResultOracleEncoding(rs, isLimit);
		} else {
			datas = extractResultDefault(rs, isLimit);
		}
		ResultModel resultModel = new ResultModel();
		resultModel.setColumns(columns);
		resultModel.setResults(datas);
		resultModel.setTableList(tableSet);
		rs.close();
		stmt.close();
		return resultModel;
	}

	public int executeUpdate(String query, String connectionName) throws Exception {
		ConnectionInfo connectionInfo = connectionService.getConnectionInfo(connectionName);
		Connection connection = connectionInfo.getConnection();
		Statement stmt = connection.createStatement();
		if (connectionInfo.getDbType().contains("Oracle") && "MS949".equals(connectionInfo.getRawEncoding())) {
			//			query = new String(query.getBytes("KSC5601"), "8859_1");
		}
		int result = stmt.executeUpdate(query);
		stmt.close();
		return result;
	}

	private LinkedList<LinkedList<String>> extractResultDefault(ResultSet rs, boolean isLimit) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int colSize = rsmd.getColumnCount();
		LinkedList<LinkedList<String>> datas = new LinkedList<LinkedList<String>>();
		int row = 0;
		while (rs.next()) {
			LinkedList<String> result = new LinkedList<String>();
			for (int i = 1; i <= colSize; i++) {
				String str = rs.getString(i);
				if (str == null) {
					str = "NULL";
				}
				result.add(str);
			}
			datas.add(result);
			row++;
			if (row == maxRow && isLimit) {
				break;
			}
		}
		return datas;
	}

	private LinkedList<LinkedList<String>> extractResultOracleEncoding(ResultSet rs, boolean isLimit) throws SQLException, UnsupportedEncodingException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int colSize = rsmd.getColumnCount();
		LinkedList<LinkedList<String>> datas = new LinkedList<LinkedList<String>>();
		int row = 0;
		while (rs.next()) {
			LinkedList<String> result = new LinkedList<String>();
			for (int i = 1; i <= colSize; i++) {
				String str = rs.getString(i);
				if (str == null) {
					str = "NULL";
				}
				str = new String(str.getBytes("8859_1"), "KSC5601");
				result.add(str);
			}
			datas.add(result);
			row++;
			if (row == maxRow && isLimit) {
				break;
			}
		}
		return datas;

	}

	public ResultModel getTableList(String dbName) throws Exception {
		ConnectionInfo connectionInfo = connectionService.getConnectionInfo(dbName);
		String query = null;
		String dbType = DBPMUtil.getDBType(connectionInfo.getDbType());
		if (dbType.equals("Oracle")) {
			query = "select object_name from user_objects where object_type = 'TABLE'";
			ResultModel result = executeQuery(query, dbName, false);
			if (result.getResults().size() == 0) {
				query = "SELECT owner||'.'||table_name FROM all_tables order by owner";
			}
			result = executeQuery(query, dbName, false);
			return result;
		} else {
			query = "show tables";
		}
		return executeQuery(query, dbName, false);
	}

	public ResultModel getTableDesc(String dbName, String tableName) throws Exception {
		ConnectionInfo connectionInfo = connectionService.getConnectionInfo(dbName);
		String query = null;
		String dbType = DBPMUtil.getDBType(connectionInfo.getDbType());
		if (dbType.equals("Oracle")) {
			int idx = tableName.indexOf(".");
			String owner = connectionInfo.getUser();
			if (idx >= 0) {
				owner = tableName.substring(0, idx);
				tableName = tableName.substring(idx + 1);
			}
			query = "select column_name, data_type, data_length, nullable, data_default from all_tab_columns where owner = '" + owner + "' and table_name = '" + tableName + "'";
		} else {
			query = "desc " + tableName;
		}
		return executeQuery(query, dbName, false);
	}

	public ResultModel getPlan(String dbName, String query) throws Exception {
		ConnectionInfo connectionInfo = connectionService.getConnectionInfo(dbName);
		String dbType = DBPMUtil.getDBType(connectionInfo.getDbType());
		if(dbType.contains("others")){
			return null;
		}
		if (dbType.equals("Oracle")) {
			executeUpdate("explain plan set statement_id = 'wdbc' for " + query, dbName);
			ResultModel result = executeQuery("	select id, parent_id,depth, operation, cost, cardinality, bytes from plan_table where statement_id = 'wdbc' and plan_id = (select max(plan_id) from plan_table where statement_id = 'wdbc')", dbName);
			return result;
		} else {
			ResultModel result = executeQuery("explain " + query, dbName);
			return result;
		}
	}

	public ResultModel getIndexList(String dbName, String tableName) throws Exception {
		ConnectionInfo connectionInfo = connectionService.getConnectionInfo(dbName);
		String query = null;
		String dbType = DBPMUtil.getDBType(connectionInfo.getDbType());
		if (dbType.equals("Oracle")) {
			int idx = tableName.indexOf(".");
			String owner = connectionInfo.getUser();
			if (idx >= 0) {
				owner = tableName.substring(0, idx);
				tableName = tableName.substring(idx + 1);
			}
			query = "SELECT A.uniqueness, b.index_name, b.column_name, b.descend FROM ALL_INDEXES a, ALL_IND_COLUMNS b WHERE a.index_name = b.index_name and a.table_owner = b.table_owner AND a.table_name=upper('" + tableName + "') AND a.table_owner = '" + owner + "' order by b.index_name";
		} else {
			query = "show index from " + tableName;
		}
		return executeQuery(query, dbName, false);
	}

	public ResultModel getContents(String dbName, String tableName, int start) throws Exception {
		ConnectionInfo connectionInfo = connectionService.getConnectionInfo(dbName);
		String dbType = DBPMUtil.getDBType(connectionInfo.getDbType());
		if (dbType.equals("Oracle")) {
			ResultModel result = executeQuery("select * from (select rownum row_num, a.* from " + tableName + " a where rownum <= " + (start + maxRow) + ") where row_num > " + start, dbName);
			return result;
		} else {
			ResultModel result = executeQuery("select * from " + tableName + " limit " + start + ", " + maxRow, dbName);
			return result;
		}
	}

	public List<String> getUniqueColumns(String dbName, String tableName) throws Exception {
		ConnectionInfo connectionInfo = connectionService.getConnectionInfo(dbName);
		String dbType = DBPMUtil.getDBType(connectionInfo.getDbType());
		ResultModel rm = getIndexList(dbName, tableName);
		List<String> results = new ArrayList<String>();
		if (dbType.equals("Oracle")) {
			for (LinkedList<String> row : rm.getResults()) {
				if ("UNIQUE".equals(row.get(0))) {
					results.add(row.get(2));
				}
			}
		} else {
			for (LinkedList<String> row : rm.getResults()) {
				if ("PRIMARY".equals(row.get(2))) {
					results.add(row.get(4));
				} else if ("0".equals(row.get(1))) {
					results.add(row.get(4));
				}
			}
		}
		return results;
	}

	public int getTotalCount(String dbName, String tableName) throws Exception {
		ConnectionInfo connectionInfo = connectionService.getConnectionInfo(dbName);
		String query = null;
		String dbType = DBPMUtil.getDBType(connectionInfo.getDbType());
		if (dbType.equals("Oracle")) {
			int idx = tableName.indexOf(".");
			if (idx >= 0) {
				tableName = tableName.substring(idx + 1);
			}
		}
		query = "select count(*) from " + tableName;
		ResultModel rm = executeQuery(query, dbName, false);
		return Integer.parseInt(rm.getResults().get(0).get(0));

	}

	public void modifyContent(String dbid, String table, String columns, String values, String mcol, String mval) throws Exception {
		StringBuilder sb = new StringBuilder("UPDATE ");
		sb.append(table).append(" SET ").append(mcol).append("= '").append(mval).append("' WHERE ");

		String[] cols = columns.split(",");
		String[] vals = values.split(",");

		int count = 0;
		for (int i = 0; i < cols.length; i++) {
			if (StringUtils.isEmpty(cols[i]) || "NULL".equals(vals[i])) {
				continue;
			}
			if (i != 0) {
				sb.append(" AND ");
			}
			sb.append(cols[i]).append("='").append(vals[i]).append("'");
			count++;
		}
		if (count == 0) {
			return;
		}
		executeUpdate(sb.toString(), dbid);
	}

}
